﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

/// <summary>
/// Summary description for EmployeeVacancy
/// </summary>
public class EmployeeMethord
{
    SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["connection"].ConnectionString);
    public EmployeeMethord()
	{
	}
    public void connClose()
    {
        if (conn.State == ConnectionState.Open)
        {
            conn.Close();
        }
    }
    //METHORD CREATEVACANCY
    public SqlDataReader ReturnTypeOfJob()
    {
        SqlDataReader Sqldr = null;
        try
        {
            SqlCommand comm = new SqlCommand("Select * from SelectElementTypeJob", conn);
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            Sqldr = comm.ExecuteReader();
        }
        catch (Exception ex)
        {
            Console.Write(ex.Message);
        }
        return Sqldr;
    }
    private string VacancyIDLast()
    {
        string VacancyID = "";
        try
        {
            SqlCommand comm = new SqlCommand("Select * from VacancyIDLast", conn);
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter(comm);
            da.Fill(ds);
            DataTable tb = ds.Tables[0];
            if (tb.Rows.Count > 0)
            {
                VacancyID = tb.Rows[tb.Rows.Count - 1]["VacancyID"].ToString();
            }
        }
        catch (Exception ex)
        {
            Console.Write(ex.Message);
        }
        finally
        {
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
            }
        }
        return VacancyID;
    }
    public string VacancyIDNew()
    {
        string VacancyID = VacancyIDLast();
        try
        {
            if (!VacancyID.Equals(""))
            {
                char[] cID = VacancyID.ToArray();
                int n = VacancyID.Count() - 1;
                VacancyID = "";
                for (int i = n; i >= 0; i--)
                {
                    try
                    {
                        int iu = Convert.ToInt32(cID[i].ToString());
                        if (iu < 9)
                        {
                            iu++;
                            VacancyID += iu;
                            for (int j = i - 1; j >= 0; j--)
                            {
                                VacancyID += cID[j].ToString();
                            }
                            break;
                        }
                        else
                        {
                            if (i == 1)
                            {
                                VacancyID += "01";
                            }
                            else
                            {
                                iu = 0;
                                VacancyID += iu;
                            }
                        }
                    }
                    catch (Exception)
                    {
                        VacancyID += cID[i].ToString();
                    }
                }
                char[] cUser = VacancyID.ToArray();
                VacancyID = "";
                for (int i = n; i >= 0; i--)
                {
                    VacancyID += cUser[i].ToString();
                }
            }
            else
            {
                VacancyID = "V0001";
            }
        }
        catch (Exception ex)
        {
            Console.Write(ex.Message);
        }
        return VacancyID;
    }
    public bool InsertTableVacancy(string _Title, int _JobID, string _UserName, string _Description, int _NumberOfJob, string _Salary, string _Address, DateTime _DateCreatioin, DateTime _DateFulfilled)
    {

        bool result = false;
        try
        {
            SqlCommand comm = new SqlCommand("EXECUTE InsertTableVacancyNew @VID, @Title, @JobID, @Username, @Description, @NumberOfJob, @Salary, @Address, @DateCreate, @DateFulfilled, @Status", conn);
            comm.Parameters.AddWithValue("@VID", VacancyIDNew());
            comm.Parameters.AddWithValue("@Title", _Title);
            comm.Parameters.AddWithValue("@JobID", _JobID);
            comm.Parameters.AddWithValue("@Username", _UserName);
            comm.Parameters.AddWithValue("@Description", _Description);
            comm.Parameters.AddWithValue("@NumberOfJob", _NumberOfJob);
            comm.Parameters.AddWithValue("@Salary", _Salary);
            comm.Parameters.AddWithValue("@Address", _Address);
            comm.Parameters.AddWithValue("@DateCreate", _DateCreatioin);
            comm.Parameters.AddWithValue("@DateFulfilled", _DateFulfilled);
            comm.Parameters.AddWithValue("@Status", 1);
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            int check = comm.ExecuteNonQuery();
            if (check > 0)
            {
                result = true;
            }
        }
        catch (Exception ex)
        {
            Console.Write(ex.Message);
        }
        finally
        {
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
            }
        }
        return result;
    }
    //METHORD CREATEINTERVIEW
    public SqlDataReader ReturnVacancyWithUserName(string _User)
    {
        SqlDataReader Sqldr = null;
        try
        {
            SqlCommand comm = new SqlCommand("EXECUTE ReturnVacancyOfUserName @UserName", conn);
            comm.Parameters.AddWithValue("@UserName", _User);
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            Sqldr = comm.ExecuteReader();
        }
        catch (Exception ex)
        {
            Console.Write(ex.Message);
        }
        return Sqldr;
    }

    public SqlDataReader ReturnApplicantWithVacancy(string _VacancyID)
    {
        SqlDataReader Sqldr = null;
        try
        {
            SqlCommand comm = new SqlCommand("EXECUTE ReturnApplicantOfVacancy @VID", conn);
            comm.Parameters.AddWithValue("@VID", _VacancyID);
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            Sqldr = comm.ExecuteReader();
        }
        catch (Exception ex)
        {
            Console.Write(ex.Message);
        }
        return Sqldr;
    }

    public SqlDataReader ReturnInterviewer()
    {
        SqlDataReader Sqldr = null;
        try
        {
            SqlCommand comm = new SqlCommand("Select * from ReturnInterviewer", conn);
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            Sqldr = comm.ExecuteReader();
        }
        catch (Exception ex)
        {
            Console.Write(ex.Message);
        }
        return Sqldr;
    }
    public bool InsertIntoTableInterview(string _InterviewerID, string _VacancyID, string _ApplicantID, DateTime _Date, string _Time)
    {
        bool result = false;
        try
        {
            SqlCommand comm = new SqlCommand("EXECUTE InsertIntoTableInterview @InterviewerID, @VacancyID, @ApplicantID, @Date, @Time, 2", conn);
            comm.Parameters.AddWithValue("@InterviewerID", _InterviewerID);
            comm.Parameters.AddWithValue("@VacancyID", _VacancyID);
            comm.Parameters.AddWithValue("@ApplicantID", _ApplicantID);
            comm.Parameters.AddWithValue("@Date", _Date);
            comm.Parameters.AddWithValue("@Time", _Time);
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            int check = comm.ExecuteNonQuery();
            if (check > 0)
            {
                result = true;
            }
        }
        catch (Exception ex)
        {
            Console.Write(ex.Message);
        }
        return result;
    }
    public string ReturnNameEmployee(string _UserName)
    {
        string Name = "";
        try
        {
            SqlCommand comm = new SqlCommand("EXECUTE ReturnNameEmployee @User", conn);
            comm.Parameters.AddWithValue("@User", _UserName);
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter(comm);
            da.Fill(ds);
            DataTable tb = ds.Tables[0];
            if (tb.Rows.Count > 0)
            {
                Name = tb.Rows[0]["FullName"].ToString();
            }
        }
        catch (Exception ex)
        {
            Console.Write(ex.Message);
        }
        return Name;
    }
    //MASTERPAGE
    public DataTable MenuTypeJob()
    {
        DataTable tb = new DataTable();
        try
        {
            SqlCommand comm = new SqlCommand("Select * from ReturnTypeJob", conn);
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            SqlDataAdapter da = new SqlDataAdapter(comm);
            DataSet ds = new DataSet();
            da.Fill(ds);
            tb = ds.Tables[0];
        }
        catch (Exception ex)
        {
            Console.Write(ex.Message);
        }
        finally
        {
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
            }
        }
        return tb;
    }

    public DataTable MenuVacancy(int _ID)
    {
        DataTable tb = new DataTable();
        try
        {
            SqlCommand comm = new SqlCommand("EXECUTE ReturnVacancyFollowTypeJob @ID", conn);
            comm.Parameters.AddWithValue("@ID", _ID);
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            SqlDataAdapter da = new SqlDataAdapter(comm);
            DataSet ds = new DataSet();
            da.Fill(ds);
            tb = ds.Tables[0];
        }
        catch (Exception ex)
        {
            Console.Write(ex.Message);
        }
        return tb;
    }
    public DataTable MenuApplicant(int _ID)
    {
        DataTable tb = new DataTable();
        try
        {
            SqlCommand comm = new SqlCommand("EXECUTE ReturnVacancyFollowTypeJob @ID", conn);
            comm.Parameters.AddWithValue("@ID", _ID);
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            SqlDataAdapter da = new SqlDataAdapter(comm);
            DataSet ds = new DataSet();
            da.Fill(ds);
            tb = ds.Tables[0];
        }
        catch (Exception ex)
        {
            Console.Write(ex.Message);
        }
        return tb;
    }
}